package dao.impl;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.ReaderDao;
import model.BorrowBook;
import model.Reader;
import model.BookReview;
import util.DateConvert;
import util.JDBCUtiles;

public class ReaderDaoImpl implements ReaderDao {

	@Override
	public boolean register(Reader rd) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "INSERT INTO reader VALUES (?,?,?,?,?,?,'')";
		int i = qr.update(sql, rd.getReaderId(),rd.getReaderType(),rd.getReaderName(),rd.getReaderPwd()
				,rd.getReaderDept(),rd.getReaderPhone());
		return  i > 0 ? true : false;
	}

	@Override
	public Reader getReaderByIdAndPwd(String id, String pwd) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT * FROM reader WHERE ReaderID = ? AND ReaderPwd = ? AND ReaderType='1'" ;
		return qr.query(sql, new BeanHandler<Reader>(Reader.class),id,pwd);
	}

	@Override
	public boolean returnByid(String uid, String bid) throws SQLException{
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "INSERT INTO bookback(ReaderID,ISBN,ReturnDate,State) VALUES(?,?,?,'待审核')";	
		Date date = new Date();
		int i = qr.update(sql,uid,bid,date);
		return  i > 0 ? true : false;
	}

	@Override
	public List queryReturnStateById(String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT bookback.ISBN,booktype.TypeName,book.BookName,book.Author,book.Publish,book.UnitPrice,book.Count,bookback.State "
				+ "FROM book,bookback,booktype "
				+ "WHERE book.ISBN = bookback.ISBN AND book.TypeID = booktype.TypeID AND bookback.State='待审核' AND bookback.ReaderID=?" ;
		return qr.query(sql, new BeanListHandler<BorrowBook>(BorrowBook.class),uid);
	}

	@Override
	public List queryReturnedById(String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT bookback.ISBN,booktype.TypeName,book.BookName,book.Author,book.Publish,book.UnitPrice,book.Count,bookback.State "
				+ "FROM book,bookback,booktype "
				+ "WHERE book.ISBN = bookback.ISBN AND book.TypeID = booktype.TypeID AND bookback.State='已还' AND bookback.ReaderID=?" ;
		return qr.query(sql, new BeanListHandler<BorrowBook>(BorrowBook.class),uid);
	}

	@Override
	public Reader getReaderById(String id) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT * FROM reader WHERE ReaderID = ?" ;
		return qr.query(sql, new BeanHandler<Reader>(Reader.class),id);
	}

	@Override
	public boolean reset(String id, String name, String dept, int phone) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE reader SET ReaderName=?,ReaderDept=?,ReaderPhone=? WHERE ReaderID=?";	
		Date date = new Date();
		int i = qr.update(sql,name,dept,phone,id);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean resetPwd(String uid, String pass , String newPass) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE reader SET ReaderPwd=? WHERE ReaderID=? AND ReaderPwd=?";	
		Date date = new Date();
		int i = qr.update(sql,newPass,uid,pass);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean delReaderByIdAndPwd(String uid, String pass) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "DELETE FROM reader WHERE ReaderID=? AND ReaderPwd=?";	
		Date date = new Date();
		int i = qr.update(sql,uid,pass);
		return  i > 0 ? true : false;
	}

	@Override
	public boolean review(String bid, String uid, int grade, String desc) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "INSERT INTO review(ISBN,ReaderID,Grade,Talk) VALUES(?,?,?,?)";	
		int i = qr.update(sql,bid,uid,grade,desc);
		return  i > 0 ? true : false;
	}

	@Override
	public List queryAllReview() throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT reader.ReaderName,review.ISBN,book.BookName,review.Grade,review.Talk "
				+ "FROM reader,review,book "
				+ "WHERE reader.ReaderID=review.ReaderID AND review.ISBN=book.ISBN" ;
		return qr.query(sql, new BeanListHandler<BookReview>(BookReview.class));
	}

	@Override
	public List queryReviewById(String id) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "select reader.ReaderName,review.ISBN,book.BookName,review.Grade,review.Talk "
				+ "FROM reader,review,book "
				+ "WHERE reader.ReaderID=review.ReaderID AND review.ISBN=book.ISBN AND reader.ReaderID=?" ;
		return qr.query(sql, new BeanListHandler<BookReview>(BookReview.class),id);
	}

}
